﻿var util = require('util');
var mysql = require('mysql');
var async = require('async');
var config = require('./construct/config.js');
var crypto = require('crypto');
var fs = require('fs');



var publicPem = fs.readFileSync('cert.pem');

function content(c) {
    var key = null;
    if (c == "public")
        key = publicPem.toString();
    return key;
}

var decrypt = function (crypted) {
    var key = "www.sxzd365.com_";//content("public");
    key = key.toString('hex');
    var iv = "1234567890abcdef";
    crypted = new Buffer(crypted, 'base64').toString('binary');
    var decipher = crypto.createDecipheriv('aes-128-cbc', key, iv);
    var decoded = decipher.update(crypted, 'binary', 'utf8');
    decoded += decipher.final('utf8');
    return decoded;
};
//签名认证
function decode_rsa_verify(data, sig) {
    var verify = crypto.createVerify('RSA-SHA256');
    verify.update(data);
    return verify.verify(content("public"), sig, 'hex');
}
//sig 签名

//sig 签名
function decode_Connect() {
    var c = null;
    var obj = config.connect;
    var ret = decode_rsa_verify(obj.encrypt, obj.sig);
    if (ret) {
        c = decrypt(obj.encrypt);
    }
    return c;
}


var Db = {
    database:null,
    pool: null,
    init_flag: false,
    init: function (db_config) {
        if (db_config == null || db_config == '') {
            var ret = JSON.parse(decode_Connect());
            pool = mysql.createPool(ret);
            pool.database = ret.database;
        }
        else
            pool = mysql.createPool(db_config);
        return pool;
    },
    createNew: function (db_config) { 
        //var pool = null;
        if (this.init_flag == false) {
            pool = this.init(db_config);
            this.init_flag = true;
        }
        var db = {
            tableName: "",
            selectS: "",
            whereS: "",
            orerbyS: "",
            limitS: "",
            querySql: "",
            countSql: "",
            groupbyS: "",
            havingS: "",
            distinctS: false,
            trans: false,
            sqlparamsEntities:[],
            
            setNull: function () {
                this.tableName = "";
                this.selectS = "";
                this.whereS = "";
                this.orerbyS = "";
                this.limitS = "";
                this.querySql = "";
                this.countSql = "";
                this.groupbyS = "";
                this.havingS = "";
                this.distinctS = false;


            },
            //得到所有查询组合的语句
            get: function (tbName, offset, rows) {

                if (tbName) {

                    this.tableName = tbName;
                }

                if (rows) {

                    this.limit(offset, rows);
                }

                this.getSql();

            },
            //简单查询  data为对象
            get_where: function (tbName, data) {

                if (tbName) {
                    this.tableName = tbName;
                };
                if (data) {

                    this.where(data);
                };

                this.getSql();
            },

            trans_start:function(){
            
                this.trans = true;
            
            },

            where_in: function (field,dataArray) {

                var i ,wx="";
                if (this.whereS == "") {

                    this.whereS = " WHERE " + field + " IN (";
                } else {

                    wx = ' AND '+ field + ' IN (';
                }
                
                // console.log(dataArray.length);
                for (i = 0; i < dataArray.length;i++) {
                    
                    wx += pool.escape(dataArray[i]);
                    if (dataArray.length != i+1) {
                        wx += " , ";
                    } else {

                        wx += " ) ";
                    }
                    
                }
                this.whereS += wx;

            },

            //  自定义字符串:  $where = "name='Joe' AND status='boss' OR status='active'"; db.where($where);
            //  对象的形式 {id:2,num:">3",num2:"=8",num3:"2,3" }  属性之间
            //  这个代码有改动：2017-12-05 钱总
            where: function (whereData) {
                if (typeof (whereData) == "string") {
                    this.whereS = whereData;
                    return;
                }
                if(typeof(whereData) !='object'){
                    console.log("where func wheredata not and object!");
                    return;
                }

                if (this.whereS == "") {
                    this.whereS = " WHERE 1=1 ";
                }

                var fh = [">=","<=","!=","=",">","<"];

                for (var key in whereData) {
                    var value = whereData[key]+'';
                    value = value.replace(/(^\s*)|(\s*$)/g, "");
                    if (value == '' || value == ',') {
                       continue;
                    }
                    var wx = '';
                    var real_data;
                    var cp = "";
                    var find_fh = 0;
                    for(var i = 0;i< fh.length;i++){
                        var len = fh[i].length;
                        cp = value.substr(0,len);
                        if(fh[i] === cp){
                            real_data = value.substr(len, value.length - len);
                            real_data = real_data.replace(/(^\s*)|(\s*$)/g, "");
                            find_fh = 1;
                            break;    
                        }
                    }                                    
                    if (real_data == '') {
                        continue;
                    }
                    wx += " AND ";
                    wx += key;                                       
                    if(find_fh == 0){
                        var num = value.indexOf(",");
                        if(num !== -1)
                        {
                            var f = value.substr(0, num);
                            f = f.replace(/(^\s*)|(\s*$)/g, "");
                            var t = value.substr(num + 1, value.length - num);
                            t = t.replace(/(^\s*)|(\s*$)/g, "");
                            if (f == "" && t != "")
                                wx += " <= " + pool.escape(t);
                            else if (f != "" && t == "")
                                wx += " >= " + pool.escape(f);
                            else if (f != "" && t != "")
                                wx += " between '" + f  + "' and '" + t + "'";    
                        }
                        else{
                            wx += " like ";
                            var likes = "%" + value + "%"
                            wx += pool.escape(likes);
                        }
                    }
                    else{                        
                            wx += cp ;
                            wx += pool.escape(real_data);                    
                    }    
                    this.whereS += wx;                                    
                }
                if(this.whereS == " WHERE 1=1 "){
                    this.whereS = "";              
                }
            },
            where_is_null:function(str){
                if (str != null) {
                    this.whereS += str;
                }              
            },
            or_where: function (whereData) {
               
                if (this.whereS == "") {

                    this.whereS = " WHERE 1!=1 ";
                }

                for (var key in whereData) {
                    var wx = '';
                    wx += " OR ";
                    wx += key;
                    if (key.indexOf("!=") !== -1 || key.indexOf(">") !== -1 || key.indexOf("<") !== -1 || key.indexOf("=") !== -1) {

                        wx += " ";
                    } else {

                        wx += " = ";
                    }

                    wx += pool.escape(whereData[key]);
                    this.whereS += wx;
                }


            },
            select: function (select) {

                if (select == "") {
                    this.selectS = "*";
                } else {

                    this.selectS = select;
                }

            },
            distinct: function () {

                this.distinctS = true;
            },
            limit: function (offset, rows) {

                this.limitS = " LIMIT " + offset + "," + rows;

            },
            like: function (whereData) {

                if (this.whereS == "") {

                    this.whereS = " WHERE 1=1 ";
                }

                for (var key in whereData) {
                    var wx = '';
                    wx += " AND ";
                    wx += key;
                    wx += " LIKE ";
                    var likes = "%" + whereData[key] + "%"
                    wx += pool.escape(likes);
                    //wx += "  escape ";
                    this.whereS += wx;
                }

            },

            or_like: function (whereData) {
                if (this.whereS == "") {

                    this.whereS = " WHERE 1!=1 ";
                }

                for (var key in whereData) {
                    var wx = '';
                    wx += " OR ";
                    wx += key;
                    wx += " LIKE ";
                    var likes = "%" + whereData[key] + "%"
                    wx += pool.escape(likes);
                    //wx += "  escape ";
                    this.whereS += wx;
                }

            },
            order_by: function (field, sort) {

                this.orerbyS = " ORDER BY " + field + " " + sort + " ";

            },
            group_by: function (fieldNname) {

                this.groupbyS = " GROUP BY " + fieldNname + " ";

            },
            having: function (havingSql) {

                this.havingS = " HAVING  " + havingSql + " ";
            },
            from: function (tbName) {

                this.tableName = tbName;
            },
            getSql: function () {

                var sql = "";
                if (this.selectS == "") {

                    this.selectS = "*";
                }

                if (this.tableName == "") {

                    console.log("table name is null ");
                    return;
                }
                if (this.distinctS) {

                    sql = "SELECT  DISTINCT " + this.selectS + " FROM " + this.tableName + " ";
                } else {

                    sql = "SELECT  " + this.selectS + " FROM " + this.tableName + " ";
                }
                
                var coSql = "SELECT  COUNT(*) FROM  " + this.tableName;

                var queryCon = "";
                var queryCountSql = "";
                if (this.whereS != "") {

                    queryCon += this.whereS;
                    queryCountSql += this.whereS;
                }

                if (this.groupbyS  != "") {

                    queryCon += this.groupbyS;
                    queryCountSql += this.groupbyS;
                }

                if (this.havingS != "") {

                    queryCon += this.havingS;
                    queryCountSql += this.havingS;
                }
                if (this.orerbyS != "") {

                    queryCon += this.orerbyS;

                }
                if (this.limitS != "") {

                    queryCon += this.limitS;
                }
                this.setNull();
                this.countSql = "";
                this.querySql = sql + queryCon;
                this.countSql = coSql + queryCountSql;
            },

            count_all_results: function (callback) {
                if (this.countSql == "") {

                    console.log("countSql is null return ....");
                    return;
                }

                var sql = this.countSql;               
               // this.countSql = "";
                db.querys(sql, null, function (err, results, fileld) {
                    if (err) {

                        console.log("err sql AAA:",sql);
                        callback(err);
                    } else {
                        callback(1, results[0]['COUNT(*)'])
                    }

                })


            },
            //返回数组    
            result: function (callback) {

                var sql = this.querySql;
               // this.setNull();
               // console.log("the sql is :",sql);
                db.querys(sql, { timeout: 60000 }, function (err, results, fileld) {
                    if (err) {

                        console.log("err sql:" + sql);
                        callback(-1,err);
                    } else {
                            var ret = JSON.stringify(results);
                            callback(1, JSON.parse(ret))
                                                
                    }

                })

            },
            //返回对象，如果是多行，就返回第一行    
            row: function (callback) {

                var sql = this.querySql;
                // this.setNull();

                db.querys(sql, null, function (err, results, fileld) {
                    if (err) {

                        console.log("err sql:" + sql);
                        callback(-1,err);
                    } else {
                        if (results.length === 0) {

                            callback(-1, null)
                        } else {

                            var ret = JSON.stringify(results[0]);
                            callback(1, JSON.parse(ret))
                        }
                        
                    }

                })

            }

        };

        //sql语句查询
        db.querys = function query(sql, options, callback) {
            pool.getConnection(function (err, conn) {
                if (err) {

                    callback(err, null, null);
                } else {

                    conn.query(sql, options, function (err, results, fields) {
                        //console.log("my results",results);
                        //事件驱动回调  
                        callback(err, results, fields);                        //释放连接  
                        conn.release();

                    });
                }
            });
        };

        //事务处理
        db.execTrans = function(callback) {
            pool.getConnection(function (err, connection) {
                if (err) {
                    return callback(err, null);
                }
                connection.beginTransaction(function (err) {
                    if (err) {
                        return callback(err, null);
                    }
                    if (db.sqlparamsEntities.length === 0) {

                        console.log("sql is null ");
                        return callback(err, null);
                    }
                    console.log("开始执行transaction，共执行" + db.sqlparamsEntities.length + "条数据");
                    var funcAry = [];
                    db.sqlparamsEntities.forEach(function (sql_param) {
                        var temp = function (cb) {
                            var sql = sql_param.sql;
                          
                            connection.query(sql, null, function (tErr, rows, fields) {
                                if (tErr) {
                                    connection.rollback(function () {
                                        console.log("事务失败，" + sql_param + "，ERROR：" + tErr);
                                        throw tErr;
                                    });
                                } else {
                                    return cb(null, 'ok');
                                }
                            })
                        };
                        funcAry.push(temp);
                    });
                   db.sqlparamsEntities = [];
                   db.trans = false;
                    async.series(funcAry, function (err, result) {
                        console.log("transaction error: " + err);
                        if (err) {
                            connection.rollback(function (err) {
                                console.log("transaction error: " + err);
                                connection.release();
                                return callback(err, null);
                            });
                        } else {
                            connection.commit(function (err, info) {
                                console.log("transaction info: " + JSON.stringify(info));
                                if (err) {
                                    console.log("执行事务失败，" + err);
                                    connection.rollback(function (err) {
                                        console.log("transaction error: " + err);
                                        connection.release();
                                        return callback(err, null);
                                    });
                                } else {
                                    connection.release();
                                    return callback(null, info);
                                }
                            })
                        }
                    })
                });
            });
        }

        //插入   正确返回（1，插入的id） 错误（-1，err）   如果插入key重复的话 返回 插入的id为 0；
        db.insert = function (tableName, data, callback) {

            var sql = "INSERT INTO  ";
            sql += tableName;
            sql += "(";
            var values = "VALUES (";
            for (var key in data) {

                sql += key;
                sql += ",";
                values += pool.escape(data[key])
                values += ","

            }
            sql = sql.substr(0, sql.length - 1);
            values = values.substr(0, values.length - 1);
            sql += ")";
            values += ")";
            sql += values;

            // console.log(sql);
            if (this.trans) {
                
                this.sqlparamsEntities.push({ sql: sql });
                
                return;
            }
            db.querys(sql, null, function (err, results, fileld) {
                // console.log(results);
                if (err) {

                    callback(-1,err);
                } else {
                    if (results.affectedRows == 0) {
                        callback(-1, "数据重复");
                    }
                    else{
                        // console.log(results.affectedRows);
                        callback(1, results.insertId);
                    }
                }

            })

        }

        db.insert_batch = function (tableName, data, callback) {

            var sql = "INSERT IGNORE INTO  ";
            sql += tableName;
            sql += "(";
            var values = "VALUES ";
            for (var i = 0; i < data.length; i++) {
                var dataLength =  Object.getOwnPropertyNames(data[i]).length;
                values += "(";
                var j = 0;
                for (var key in data[i]) {
                    if (i === 0) {
                        sql += key;
                        sql += ",";
                    }
                    
                    values += pool.escape(data[i][key])
                    console.log(dataLength);
                    console.log(j);
                    if (dataLength-1 !== j) {

                        values += ","
                    }
                    
                    j++;

                }
                values += "),";
            }
            
            sql = sql.substr(0, sql.length - 1);
            values = values.substr(0, values.length - 1);
            sql += ")";
            
            sql += values;

            console.log(sql);
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });

                return;
            }
            db.querys(sql, null, function (err, results, fileld) {
                if (err) {

                    callback(-1, err);
                } else {

                    callback(1, results.affectedRows)
                }

            })


        }
        //替换 (insert的一种)  如果关键字或者索引 有相同的，就会把原纪录替换，没有就插入一条新数据。
        db.replace = function (tableName, data, callback) {

            var sql = "REPLACE INTO ";
            sql += tableName;
            sql += "(";
            var values = "VALUES(";
            for (var key in data) {

                sql += key;
                sql += ",";
                values += pool.escape(data[key])
                values += ","

            }
            sql = sql.substr(0, sql.length - 1);
            values = values.substr(0, values.length - 1);
            sql += ")";
            values += ")";
            sql += values;
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fileld) {
                if (err) {

                    callback(err);
                } else {

                    callback(1, results.affectedRows)
                }

            })

        }

        function update_sql(tablename, data) {
            var dataArray = Object.keys(data);
            var i = 0;
            var ret = "UPDATE ";
            ret += tablename;
            ret += " SET ";

            for (var key in data) {
                i++;
                var strtype = "";
                ret += key;
                ret += "=";
                ret += mysql.escape(data[key]);
                if (i != dataArray.length) {
                    ret += ",";
                }

            }
            return ret;
        }
        //根据条件更新
        db.update = function (tablename, data, callback) {
            if (db.whereS == null) {
                console.log("where is null, please do not kidding me");
                if(callback!=null)
                    callback(-1, "error do not delete all table");
                return;
            }
            if (data == null) {
                console.log("data is null, please do not kidding me");
                return;
            }

            var sql = update_sql(tablename, data);

            if (sql == null) {
                console.log("update sql is null, error!");
                return;
            }

            sql += db.whereS;

            db.setNull();
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("update() error! the sql is %s", sql);
                    callback(-1, err);
                } else {


                    callback(1, results.affectedRows);
                }

            });
        }

      //删除记录
        db.delete = function (tbName, callback) {          
            if (db.whereS == "") {
                console.log("where is null, please do not kidding me");
                if(callback!=null)
                    callback(-1, "error do not delete all table");
                return;
            }
            if (!tbName) {
                console.log("table name is null, please do not kidding me");
                return;
            }

            var sql = "DELETE FROM " + tbName;
            sql += db.whereS;
            db.setNull();
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("delete() error! the sql is %s", sql);
                    callback(-1, err);
                } else {


                    callback(1, results.affectedRows);
                }

            });

        }

        //删除表中所有的数据
        db.empty_table = function (tbName, callback) {

            var sql = "DELETE FROM " + tbName;
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("empty_table() error! the sql is %s", sql);
                    callback(-1, err);
                } else {


                    callback(1, results.affectedRows);
                }

            });

        }

        //获得表中所有的行数
        db.count_all = function (tbName, callback) {

            var sql = " SELECT COUNT(*) FROM " + tbName;
            console.log(sql);
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("count_all() error! the sql is %s", sql);
                    callback(-1, err);
                } else {

                    callback(1, results[0]['COUNT(*)']);

                }

            });
        }

        // 创建表的sql语句
        function sql_create(tablename, fields) {
            var ret = "CREATE TABLE IF NOT EXISTS ";
            ret += tablename;
            ret += "(";
            for (var key in fields) {
                ret += " ";
                ret += key;
                ret += " ";

                var t = fields[key].type;
                if (t == undefined) {
                    console.log("key's type not defined");
                    t = "TEXT";
                }
                ret += t;
                ret += ",";
            }
            ret = ret.substr(0, ret.length - 1);
            ret += ")";
            return ret;
        }

        // 创建一张表
        db.create_table = function (define, callback) {

            var table_field = null;

            if (define == null) {
                console.log("no define ,error!");
                return;
            }

            var tableName = define.tableName;
            var tableField = define.fields;
            var sql = sql_create(tableName, tableField);
            
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("create_table() error! the sql is %s", sql);
                    callback(-1, err);
                } else {

                    callback(1, results);

                }

            });

        }
        //删除表
        db.drop_table = function (tbName, callback) {

            var sql = "DROP TABLE IF EXISTS " + tbName;
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("drop_table() error! the sql is %s", sql);
                    callback(-1, err);
                } else {

                    callback(1, results);

                }

            });

        }

        //列出表的所有列
        db.list_fields = function (tbName, callback) {

            var sql = "SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name=" + pool.escape(tbName);
            sql += " and table_schema=" + pool.escape(pool.database);

            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("list_fields() error! the sql is %s", sql);
                    callback(-1, err);
                } else {
                    var ret = JSON.stringify(results);
                    callback(1, JSON.parse(ret))

                }

            });

        }

        //添加一个字段  fields: {name:"",des:"",type:"" after:"", first:true }
        db.add_column = function (tbName, fields, callback) {
            var sql = "";
            if (fields.after) {

                sql = "ALTER TABLE " + tbName + " ADD  " + fields.name + " " + fields.type + "  COMMENT " + pool.escape(fields.des) + " AFTER " + fields.after;

            } else if (fields.first) {

                sql = "ALTER TABLE " + tbName + " ADD  " + fields.name + " " + fields.type + "  COMMENT " + pool.escape(fields.des) + " FIRST";

            } else {

                sql = "ALTER TABLE " + tbName + " ADD  " + fields.name + " " + fields.type + "  COMMENT " + pool.escape(fields.des);

            }

            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }

            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("list_fields() error! the sql is %s", sql);
                    callback(-1, err);
                } else {

                    callback(1, results)

                }

            });

        }
        //删除一个字段
        db.drop_column = function (tbName, fieldName, callback) {

            var sql = "ALTER TABLE " + tbName + " DROP " + fieldName;
            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("drop_column() error! the sql is %s", sql);
                    callback(-1, err);
                } else {

                    callback(1, results)

                }

            });

        }
        //修改列  修改一个字段  newField {name:"",type:"",des:""}  
        db.modify_column = function (tbName, oldFieldName, newField, callback) {

            var sql = "ALTER TABLE " + tbName + " CHANGE " + oldFieldName + ' ' + newField.name;
            if (newField.type) {

                sql += ' ' + newField.type;
            } else {

                sql += '  VARCHAR(256)';
            }

            if (newField.des) {

                sql += "  COMMENT " + pool.escape(newField.des);
            }

            if (this.trans) {

                this.sqlparamsEntities.push({ sql: sql });
                return;
            }
            db.querys(sql, null, function (err, results, fields) {

                if (err) {
                    console.log("modify_column() error! the sql is %s", sql);
                    callback(-1, err);
                } else {

                    callback(1, results)

                }

            });

        }

        return db;
    }

}





module.exports = Db;


